<?php
require_once(SITE_ROOT . 'class/db/DB.class.php');
require_once(SITE_ROOT . 'class/bean/Country.class.php');

class DBCountry {
	
	public function getCountries($orderby = null) {
		$db = new DB();
		$connection = $db->connect();
		
		if ($orderby == "name") {
			$select = "SELECT `country#`, `name`, `englishname`, `seasons`, `divisions`, `activeusers`, `waitingusers`, `coolness`, `date` FROM `tcountry` ORDER BY `name`";
		}
		else $select = "SELECT `country#`, `name`, `englishname`, `seasons`, `divisions`, `activeusers`, `waitingusers`, `coolness`, `date` FROM `tcountry` ORDER BY `activeusers`, `seasons`";
		
		if ($stmt = $connection->prepare($select)) {
			$stmt->execute();
			$stmt->bind_result($countryID, $name, $englishname, $seasons, $divisions, $activeusers, $waitingusers, $coolness, $date);
			
			$countries = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country;
				$country->setCountryID($countryID);
				$country->setName($name);
				$country->setEnglishName($englishname);
				$country->setSeasons($seasons);
				$country->setDivisions($divisions);
				$country->setActiveUsers($activeusers);
				$country->setWaitingUsers($waitingusers);
				$country->setCoolness($coolness);
				$country->setDate(strtotime($date));
				$countries->append($country);
			}			
			$stmt->close();
			$db->disconnect();
			
			return $countries;
		}
	}
	
	public function saveCountries($countries) {
		$db = new DB;
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('INSERT INTO `tcountry` (`country#`, `name`, `englishname`, `seasons`, `divisions`, `activeusers`, `waitingusers`, `date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `englishname`=VALUES(`englishname`), `seasons`=VALUES(`seasons`), `divisions`=VALUES(`divisions`), `activeusers`=VALUES(`activeusers`), `waitingusers`=VALUES(`waitingusers`), `date`=VALUES(`date`)')) {
			foreach ($countries as $country) {
				$stmt->bind_param('issiiiis', $countryID, $name, $englishname, $seasons, $divisions, $activeusers, $waitingusers, $date);
				
				$countryID = $country->getCountryID();
				$name = $country->getName();
				$englishname = $country->getEnglishName();
				$seasons = $country->getSeasons();
				$divisions = $country->getDivisions();
				$activeusers = $country->getActiveUsers();
				$waitingusers = $country->getWaitingUsers();
				$date = date('Y-m-d', $country->getDate());
				
				$stmt->execute();
			}
			$stmt->close();
			$db->disconnect();
		}
	}
	
	public function updateCoolness($countries) {
		$activeusers = $this->getActiveUsers();
		
		$db = new DB;
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('UPDATE `tcountry` SET `coolness` = ? WHERE `country#` = ?')) {
			foreach ($countries as $country) {
				$stmt->bind_param('di', $coolness, $countryID);
				
				$coolness = round(($activeusers/$country->getActiveUsers()), 2);
				$countryID = $country->getCountryID();
				
				$stmt->execute();
			}
			$stmt->close();
			$db->disconnect();
		}
	}
	
	public function getActiveUsers() {
		$db = new DB;
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT SUM(`activeusers`) AS `activeusers` FROM `tcountry`')) {
			$stmt->execute();
			$stmt->bind_result($activeusers);
			
			while ($stmt->fetch()) {
				$users = $activeusers;
			}			
			$stmt->close();
			$db->disconnect();
			
			return $users;
		}
	}
	
	public function getDate() {
		$db = new DB;
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT MAX(`date`) AS `date` FROM `tcountry`')) {
			$stmt->execute();
			$stmt->bind_result($date);
			
			while ($stmt->fetch()) {
				$fetchdate = $date;
			}			
			$stmt->close();
			$db->disconnect();
			
			return strtotime($fetchdate);
		}
	}
	
}

?>